package study.week05.jdbc;

import study.week05.entity.TestStudent;
import lombok.Data;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

@Data
public class StudentJdbcUtil {
    private String driver;
    private String url;
    private String user;
    private String pwd;

    public StudentJdbcUtil(String driver, String url, String user, String pwd) throws ClassNotFoundException {
        this.driver = driver;
        this.url = url;
        this.user = user;
        this.pwd = pwd;
        init();
    }
    private void init() throws ClassNotFoundException {
        Class.forName(driver);
    }

    private Connection getConnection() throws SQLException {
        return DriverManager.getConnection(url, user, pwd);
    }

    private void close(Connection connection) throws SQLException {
        if (connection != null) {
            connection.close();
        }
    }



    public TestStudent queryById(long id ) throws SQLException {
        TestStudent student = null;
        Connection connection = null;
        try {
            connection = getConnection();
            PreparedStatement statement = connection.prepareStatement("SELECT id, student_name, student_detail  FROM test_student  WHERE id = ?");
            statement.setObject(1, id);
            ResultSet resultSet = statement.executeQuery();
            while (resultSet.next()) {
                student = new TestStudent();
                student.setId(resultSet.getLong("id"));
                student.setStudentName(resultSet.getString("student_name"));
                student.setStudentDetail(resultSet.getString("student_detail"));
            }
            return student;
        } finally {
            close(connection);
        }



    }
    public List<TestStudent> queryList() throws SQLException {
        List<TestStudent> result = new ArrayList<>();
        Connection connection = null;
        try {
            connection = getConnection();
            PreparedStatement statement = connection.prepareStatement("SELECT id, student_name, student_detail  FROM test_student  ");
            ResultSet resultSet = statement.executeQuery();
            while (resultSet.next()) {
                TestStudent student = new TestStudent();
                student.setId(resultSet.getLong("id"));
                student.setStudentName(resultSet.getString("student_name"));
                student.setStudentDetail(resultSet.getString("student_detail"));
                result.add(student);
            }
            return result;
        } finally {
            close(connection);
        }


    }
    public void addUserBatch(List<TestStudent> students) throws SQLException {
        Connection connection = null;
        try {
            connection = getConnection();
            PreparedStatement statement = connection.prepareStatement("INSERT INTO `test`.`test_student` ( `student_name`, `student_detail` ) VALUES ( ?,? );");
            for (TestStudent student : students) {
                statement.setObject(1, student.getStudentName());
                statement.setObject(2, student.getStudentDetail());
                statement.addBatch();
            }
             statement.executeBatch();
        } finally {
            close(connection);
        }
    }
    public boolean addUser(TestStudent student) throws SQLException {
        Connection connection = null;
        try {
            connection = getConnection();
            PreparedStatement statement = connection.prepareStatement("INSERT INTO `test`.`test_student` ( `student_name`, `student_detail` ) VALUES ( ?,? );");
            statement.setObject(1, student.getStudentName());
            statement.setObject(2, student.getStudentDetail());
            return statement.execute();
        } finally {
            close(connection);
        }
    }

    public boolean updateUser(TestStudent student) throws SQLException {
        Connection connection = null;
        try {
            connection = getConnection();
            PreparedStatement statement = connection.prepareStatement(
                    "UPDATE `test`.`test_student` " +
                    "SET `student_name` = ?," +
                    "`student_detail` = ?" +
                    "WHERE `id` = ?;\n"
            );
            statement.setObject(1, student.getStudentName());
            statement.setObject(2, student.getStudentDetail());
            statement.setObject(3, student.getId());
            return statement.execute();
        } finally {
            close(connection);
        }
    }

    public boolean deleteById(long id ) throws SQLException {
        Connection connection = null;
        try {
            connection = getConnection();
            PreparedStatement statement = connection.prepareStatement("delete from test_student  where id = ? ");
            statement.setObject(1, id);
            return statement.execute();
        } finally {
            close(connection);
        }
    }

    public boolean truncate() throws SQLException {
        Connection connection = null;
        try {
            connection = getConnection();
            PreparedStatement statement = connection.prepareStatement("truncate table `test`.`test_student`");
            return statement.execute();
        } finally {
            close(connection);
        }
    }

    public boolean updateUserTransaction(List<TestStudent> students) throws SQLException {
        Connection connection = null;
        try {

            connection = getConnection();
            connection.setAutoCommit(false);
            for (TestStudent student : students) {
                PreparedStatement statement = connection.prepareStatement(
                        "UPDATE `test`.`test_student` " +
                                "SET `student_name` = ?," +
                                "`student_detail` = ?" +
                                "WHERE `id` = ?;\n"
                );
                statement.setObject(1, student.getStudentName());
                statement.setObject(2, student.getStudentDetail());
                statement.setObject(3, student.getId());
                statement.execute();
            }
            connection.commit();
            return true;
        }catch (Exception ex){
            if(connection != null){
                connection.rollback();
            }
            return false;
        }finally {
            close(connection);
        }
    }
}
